/**
 * 
 */
package com.model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.fileupload.FileItemIterator;
import org.apache.commons.fileupload.FileItemStream;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.model.daemon.MetaDelete;

/**
 * 
 * Deal with car business, serve as a model in MVC
 * 
 * @author cyfex
 */
public class CarService {
	/*
	 * Notice that all parameter validation should be done in Servlet who
	 * control the visit to this model.
	 * 
	 * Transform car relevant database content to Excel file. If the process
	 * succeed, generate the excel download link. Otherwise, return null value.
	 * Before returning, carExport() should generate a daemon thread to clean
	 * the meta files.
	 */
	public static boolean carAdd(HttpServletRequest request) {
		String lpn = request.getParameter("lpn");
		String model = request.getParameter("model");
		int carry = 0;
		try {
			carry = Integer.parseInt(request.getParameter("carry"));
		} catch(Exception e) {
			return false;
		}
		boolean team;
		if( request.getParameter("team").equals("外车") || request.getParameter("team").equals("1") )
			team = true;
		else if ( request.getParameter("team").equals("本校") || request.getParameter("team").equals("0") )
			team = false;
		else {
			return false;
		}
		
		if( lpn==""||model==""||carry==0||carry<0 ) {
			return false;
		}
	
		//Connect DB
		try {
			Connection con = DataBase.getConnection();
			PreparedStatement pstmtEdit = con.prepareStatement("INSERT INTO car VALUES(?,?,?,?,?)");
			pstmtEdit.setString(1, lpn);
			pstmtEdit.setBoolean(2, team);
			pstmtEdit.setInt(3, carry);
			pstmtEdit.setInt(4, 0);		//	initial 'status' column
			pstmtEdit.setString(5, model);
			
			pstmtEdit.execute();
			
			//close work
			con.close();
			return true;
		} catch (SQLException e2) {
			e2.printStackTrace();
			return false;
		}
	}
	
	public static boolean carBack(HttpServletRequest request) {
		String[] backLpns;
		backLpns = request.getParameterValues("backid");

		if( backLpns==null ) {
			return false;
		}

		//Connect DB
		try {
			Connection con = DataBase.getConnection();
			PreparedStatement pstmtCoid = con.prepareStatement("SELECT coid FROM carry WHERE lpn=? ORDER BY TIME DESC LIMIT 0,1");
			PreparedStatement pstmtIn = con.prepareStatement("UPDATE carry SET back=? WHERE coid=?");	// for table carry_out, add a record
			PreparedStatement pstmtInStatus = con.prepareStatement("UPDATE car SET status=0 WHERE lpn=?");		// for table car, edit the corresponding status.
			
			for(int i=0; i<backLpns.length; i++) {
				
				pstmtCoid.setString(1, backLpns[i]);
				ResultSet rsCoid = pstmtCoid.executeQuery();
				if( rsCoid.next() ) {
					
					pstmtIn.setTimestamp(1, new Timestamp(new java.util.Date().getTime()));
					pstmtIn.setLong(2, rsCoid.getLong(1));
					pstmtIn.execute();
					
					pstmtInStatus.setString(1, backLpns[i]);
					pstmtInStatus.execute();
				}
				rsCoid.close();
			}
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
			return false;
		}

		return true;
	}
	
	public static boolean carDelete(HttpServletRequest request) {
		String[] delLpns;
		delLpns = request.getParameterValues("delid");
		
		if( delLpns==null ) {
			return false;
		}
		
		//Connect DB
		try {
			Connection con = DataBase.getConnection();
			PreparedStatement pstmtDel = con.prepareStatement("DELETE FROM car WHERE lpn=?");
			for(int i=0; i<delLpns.length; i++) {
				pstmtDel.setString(1, delLpns[i]);
				pstmtDel.execute();
			}
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
			return false;
		}
		
		return true;
	}
	
	public static boolean carEdit(HttpServletRequest request) {
		String lpn = request.getParameter("lpn");
		String model = request.getParameter("model");
		int carry = Integer.parseInt(request.getParameter("carry"));
		boolean team;
		if( request.getParameter("team").equals("外车") || request.getParameter("team").equals("1") )
			team = true;
		else if ( request.getParameter("team").equals("本校") || request.getParameter("team").equals("0") )
			team = false;
		else {
			return false;
		}
		
		//Connect DB
		try {
			Connection con = DataBase.getConnection();
			PreparedStatement pstmtEdit = con.prepareStatement("UPDATE car SET model=?,carry=?,team=? WHERE lpn=?");
			pstmtEdit.setString(1, model);
			pstmtEdit.setInt(2, carry);
			pstmtEdit.setBoolean(3, team);
			pstmtEdit.setString(4, lpn);
			
			pstmtEdit.execute();
			
			//close work
			con.close();
			return true;
		} catch (SQLException e2) {
			e2.printStackTrace();
			return false;
		}
	}
	
	public static String carExport(HttpServletRequest request) {
		// Connect Database
		try {
			Connection con = DataBase.getConnection();

			/*
			 * Create the excel file, obtaining data from DB
			 */
			String excelPath = request.getSession().getServletContext()
					.getRealPath("downloads");
			File excelDir = new File(excelPath);
			if (!excelDir.exists()) {
				excelDir.mkdir();
			}
			Workbook wb = new HSSFWorkbook();
			Sheet sheetCar = wb.createSheet("车辆信息");
			Sheet sheetCarry = wb.createSheet("出车信息");

			/*
			 * sheetCar part
			 */
			Row rowCarTitle = sheetCar.createRow((short) 0);
			rowCarTitle.createCell(0).setCellValue("车牌号");
			rowCarTitle.createCell(1).setCellValue("车队");
			rowCarTitle.createCell(2).setCellValue("可载人数");
			rowCarTitle.createCell(3).setCellValue("状态");
			rowCarTitle.createCell(4).setCellValue("型号");
			PreparedStatement psCar = con
					.prepareStatement("SELECT lpn,team,carry,status,model FROM car");
			ResultSet rsCar = psCar.executeQuery();
			String carCarry, carModel;
			int i = 1;
			while (rsCar.next()) {
				// add a new line
				Row rowCar = sheetCar.createRow(i);
				i++;
				// add content of car information
				rowCar.createCell(0).setCellValue(rsCar.getString(1));
				rowCar.createCell(1).setCellValue(
						rsCar.getBoolean(2) ? "本校" : "外车");
				if ((carCarry = rsCar.getString(3)) != null)
					rowCar.createCell(2).setCellValue(carCarry);
				rowCar.createCell(2).setCellValue(rsCar.getInt(3));
				rowCar.createCell(3).setCellValue(
						rsCar.getBoolean(4) ? "出车" : "待命");
				if ((carModel = rsCar.getString(5)) != null)
					rowCar.createCell(4).setCellValue(carModel);
			}
			rsCar.close();

			/*
			 * sheetCarry part
			 */
			Row rowCarryTitle = sheetCarry.createRow((short) 0);
			rowCarryTitle.createCell(0).setCellValue("车牌号");
			rowCarryTitle.createCell(1).setCellValue("用车人");
			rowCarryTitle.createCell(2).setCellValue("部门");
			rowCarryTitle.createCell(3).setCellValue("出发地");
			rowCarryTitle.createCell(4).setCellValue("目的地");
			rowCarryTitle.createCell(5).setCellValue("原因");
			rowCarryTitle.createCell(6).setCellValue("出发时间");
			rowCarryTitle.createCell(7).setCellValue("回车时间");
			rowCarryTitle.createCell(8).setCellValue("备注");
			PreparedStatement psCarry = con
					.prepareStatement("SELECT lpn,user,department,`from`,`to`,reason,time,back,remark FROM carry");
			ResultSet rsCarry = psCarry.executeQuery();
			int j = 1;
			String crDepartment, crFrom, crReason, crBack, crRemark;
			while (rsCarry.next()) {
				// add a new line
				Row rowCarry = sheetCarry.createRow(j);
				j++;
				// add content of carry information
				rowCarry.createCell(0).setCellValue(rsCarry.getString(1));
				rowCarry.createCell(1).setCellValue(rsCarry.getString(2));
				if ((crDepartment = rsCarry.getString(3)) != null)
					rowCarry.createCell(2).setCellValue(crDepartment);
				if ((crFrom = rsCarry.getString(4)) != null)
					rowCarry.createCell(3).setCellValue(crFrom);
				rowCarry.createCell(4).setCellValue(rsCarry.getString(5));
				if ((crReason = rsCarry.getString(6)) != null)
					rowCarry.createCell(5).setCellValue(crReason);
				rowCarry.createCell(6).setCellValue(rsCarry.getString(7));
				if ((crBack = rsCarry.getString(8)) != null)
					rowCarry.createCell(7).setCellValue(crBack);
				if ((crRemark = rsCarry.getString(9)) != null)
					rowCarry.createCell(8).setCellValue(crRemark);
			}
			rsCarry.close();

			// Absolute path
			excelPath = request
					.getSession()
					.getServletContext()
					.getRealPath(
							"downloads" + File.separatorChar + "car-export-"
									+ System.currentTimeMillis())
					+ ".xls";
			FileOutputStream fileOut = new FileOutputStream(excelPath);
			wb.write(fileOut);
			fileOut.close();

			// Start the singleton daemon thread.
			MetaDelete.getInSingleton(request);

			// Context path
			excelPath = excelPath.substring(request.getSession()
					.getServletContext().getRealPath("/").length());
			excelPath = excelPath.replaceAll("\\\\", "/");

			// Return the context path
			return excelPath;
		} catch (Exception e) {
			e.printStackTrace();
			MetaDelete.getInSingleton(request);
			return null;
		}
	}

	public static boolean carImport(HttpServletRequest request)
			throws IOException {
		/*
		 * UploadFile
		 */
		// Check that we have a file upload request
		boolean isMultipart = ServletFileUpload.isMultipartContent(request);
		if (!isMultipart) {
			return false;
		}
		// Create a new file upload handler
		ServletFileUpload upload = new ServletFileUpload();
		// Parse the request
		FileItemIterator iter;
		String uploadedPath = null;
		try {
			iter = upload.getItemIterator(request);
			while (iter.hasNext()) {
				FileItemStream item = iter.next();
				InputStream stream = item.openStream();
				if (!item.isFormField()) {
					// Process the input stream
					uploadedPath = request.getSession().getServletContext()
							.getRealPath("uploaded");
					File uploadedExcel = new File(uploadedPath);
					if (!uploadedExcel.exists()) {
						uploadedExcel.mkdir();
					}
					// Upload file
					uploadedPath = request
							.getSession()
							.getServletContext()
							.getRealPath(
									"uploaded" + File.separatorChar
											+ System.currentTimeMillis());
					OutputStream os = new FileOutputStream(uploadedPath);
					byte buf[] = new byte[1024];
					int length = 0;
					while ((length = stream.read(buf)) > 0) {
						os.write(buf, 0, length);
					}
					// Close work
					os.flush();
					os.close();
					stream.close();
				}
			}
		} catch (FileUploadException e) {
			e.printStackTrace();
			return false;
		}

		/*
		 * Insert from excel into database
		 */
		// Connect DB
		try {

			Connection con = DataBase.getConnection();
			PreparedStatement pstmtCheck = con
					.prepareStatement("SELECT * FROM car WHERE lpn=?");
			ResultSet rsCheck = null;
			PreparedStatement pstmtImport = con
					.prepareStatement("INSERT INTO car VALUES(?,?,?,?,?)");
			PreparedStatement pstmtUpdate = con
					.prepareStatement("UPDATE car SET team=?,carry=?,model=? WHERE lpn=?");

			HSSFWorkbook wb = null;
			try {
				wb = new HSSFWorkbook(new FileInputStream(uploadedPath));
			} catch (Exception e) {
				return false;
			}
			for (int k = 0; k < wb.getNumberOfSheets(); k++) {
				HSSFSheet sheet = wb.getSheetAt(k);
				int rows = sheet.getPhysicalNumberOfRows();
				for (int r = 0; r < rows; r++) {
					HSSFRow row = sheet.getRow(r);
					if (row == null) {
						continue;
					}

					HSSFCell cellLpn = row.getCell(0);
					HSSFCell cellTeam = row.getCell(3);
					HSSFCell cellCarry = row.getCell(2);
					HSSFCell cellModel = row.getCell(1);

					String lpn = cellLpn.getStringCellValue();
					boolean team = false;

					try {
						if (cellTeam.getStringCellValue() == null)
							team = false;
						else if (cellTeam.getStringCellValue().equals("本校"))
							team = false;
						else if (cellTeam.getStringCellValue().equals("外车"))
							team = true;
					} catch (IllegalStateException e) {
						// Indicate that cellTeam is a numeric cell
						if (cellTeam.getNumericCellValue() > 1)
							team = true;
						else if (Math.abs(cellTeam.getNumericCellValue()) < 0.1)
							team = false;
						else
							;// TODO warn user there's problem on team column;
					}
					int carry = (int) cellCarry.getNumericCellValue();
					String model = cellModel.getStringCellValue();

					pstmtCheck.setString(1, lpn);
					rsCheck = pstmtCheck.executeQuery();
					if (rsCheck.next()) {
						pstmtUpdate.setBoolean(1, team);
						pstmtUpdate.setInt(2, carry);
						pstmtUpdate.setString(3, model);
						pstmtUpdate.setString(4, lpn);
						pstmtUpdate.execute();
					} else {
						pstmtImport.setString(1, lpn);
						pstmtImport.setBoolean(2, team);
						pstmtImport.setInt(3, carry);
						pstmtImport.setBoolean(4, false); // set initial
															// status=0
						pstmtImport.setString(5, model);
						pstmtImport.execute();
					}
					rsCheck.close();
				}
			}

			// close work
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
			return false;
		}

		/*
		 * Delete uploaded file
		 */
		File uploadedExcel = new File(uploadedPath);
		if (uploadedExcel.exists()
				&& !uploadedExcel.equals(request.getSession()
						.getServletContext().getRealPath("uploaded")))
			uploadedExcel.delete();

		/*
		 * Page redirection
		 */
		return true;
	}
	
	public static Boolean carOut(HttpServletRequest request) {
		String lpn = request.getParameter("lpn");
		String user = request.getParameter("user");
		String department = request.getParameter("dptmt");
		String from = request.getParameter("from");
		String to = request.getParameter("to");
		String time = request.getParameter("time");
		String reason = request.getParameter("reason");
		String remark = request.getParameter("memo");
		
		//Connect DB
		try {
			Connection con = DataBase.getConnection();
			PreparedStatement pstmtLpnCheck = con.prepareStatement("SELECT * FROM car WHERE lpn=?");
			PreparedStatement pstmtOut = con.prepareStatement("INSERT INTO carry VALUES(null,?,?,?,?,?,?,?,?,null)");	// for table carry_out, add a record
			PreparedStatement pstmtOutStatus = con.prepareStatement("UPDATE car SET status=1 WHERE lpn=?");		// for table car, edit the corresponding status.
			pstmtLpnCheck.setString(1, lpn);
			ResultSet rsLpnCheck = pstmtLpnCheck.executeQuery();
			
			if( (rsLpnCheck.next()) && (user!=null) && (department!=null) ) {
				
				// for pstmtOut
				pstmtOut.setString(1, lpn);
				pstmtOut.setString(2, user);
				pstmtOut.setString(3, department);
				pstmtOut.setString(4, from);
				pstmtOut.setString(5, to);
				if( time.equals("") ) {
					pstmtOut.setTimestamp(6, new Timestamp(new java.util.Date().getTime()));
				}
				else
					pstmtOut.setString(6, time);
				pstmtOut.setString(7, reason);
				pstmtOut.setString(8, remark);
				
				//for pstmtOutStatus
				pstmtOutStatus.setString(1, lpn);
				
				//execute both sql clauses (Table car & Table carry_out)
				pstmtOut.execute();
				pstmtOutStatus.execute();
				
				//close work
				rsLpnCheck.close();
				con.close();
				return true;
			}
			else {
				rsLpnCheck.close();
				con.close();
				return false;
			}
		} catch (SQLException e2) {
			e2.printStackTrace();
			return false;
		}
	}

}
